﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using model;
using System.Data;
using System.Data.SqlClient;

namespace dao
{
    public class BaivietDao:BaseDao
    {
        public List<BaivietModel> getAllBaiviet()
        {
            sqlCmd.CommandType = CommandType.StoredProcedure;
            sqlCmd.CommandText = "sp_baiviet_SelectAll";
            sqlCmd.Parameters.Clear();
            sqlCmd.Connection.Close();
            sqlCmd.Connection.Open();

            using (SqlDataReader dr = sqlCmd.ExecuteReader())
            {
                List<BaivietModel> glstBaiviet = new List<BaivietModel>();
                if (dr.HasRows)
                {
                    while (dr.Read())
                    {
                        BaivietModel bv = new BaivietModel();
                        bv.ID = Convert.ToInt64(dr["ID"]);
                        bv.Mabaiviet = Convert.ToString(dr["Mabaiviet"]);
                        bv.Tenbaiviet = Convert.ToString(dr["Tenbaiviet"]);
                        bv.Shortcut = Convert.ToString(dr["shortcut"]);
                        bv.Noidung = Convert.ToString(dr["noidung"]);
                        bv.Anhdaidien = Convert.ToString(dr["anhdaidien"]);
                        bv.Tenloaibaiviet = Convert.ToString(dr["tenloaibaiviet"]);
                        bv.IDLoaibaiviet = Convert.ToInt64(dr["IDLoaibaiviet"]);
                        bv.Nguoiviet = Convert.ToString(dr["nguoiviet"]);
                        bv.Ngayviet = Convert.ToDateTime(dr["ngayviet"]);
                        glstBaiviet.Add(bv);
                    }
                    dr.Close();
                    sqlCmd.Connection.Close();
                }
                return glstBaiviet;
            }
        }

        public List<BaivietModel> getBaivietTop1ByLoai(long idLoaibai)
        {
            sqlCmd.CommandType = CommandType.StoredProcedure;
            sqlCmd.CommandText = "sp_baiviet_SelectTop1ByLoaibai";
            sqlCmd.Parameters.Clear();

            sqlCmd.Parameters.Add("@IDLoaibaiviet", SqlDbType.BigInt).Value = idLoaibai;
            sqlCmd.Connection.Close();
            sqlCmd.Connection.Open();

            using (SqlDataReader dr = sqlCmd.ExecuteReader())
            {
                List<BaivietModel> glstBaiviet = new List<BaivietModel>();
                if (dr.HasRows)
                {
                    while (dr.Read())
                    {
                        BaivietModel bv = new BaivietModel();
                        bv.ID = Convert.ToInt64(dr["ID"]);
                        bv.Mabaiviet = Convert.ToString(dr["Mabaiviet"]);
                        bv.Tenbaiviet = Convert.ToString(dr["Tenbaiviet"]);
                        bv.Shortcut = Convert.ToString(dr["shortcut"]);
                        bv.Noidung = Convert.ToString(dr["noidung"]);
                        bv.Anhdaidien = Convert.ToString(dr["anhdaidien"]);
                        bv.IDLoaibaiviet = Convert.ToInt64(dr["IDLoaibaiviet"]);
                        bv.Nguoiviet = Convert.ToString(dr["nguoiviet"]);
                        bv.Ngayviet = Convert.ToDateTime(dr["ngayviet"]);
                        glstBaiviet.Add(bv);
                    }
                    dr.Close();
                    sqlCmd.Connection.Close();
                }
                return glstBaiviet;
            }
        }

        public List<BaivietModel> getBaivietByLoai(long idLoaibai)
        {
            sqlCmd.CommandType = CommandType.StoredProcedure;
            sqlCmd.CommandText = "sp_baiviet_SelectByLoaibai";
            sqlCmd.Parameters.Clear();

            sqlCmd.Parameters.Add("@IDLoaibaiviet", SqlDbType.BigInt).Value = idLoaibai;
            sqlCmd.Connection.Close();
            sqlCmd.Connection.Open();

            using (SqlDataReader dr = sqlCmd.ExecuteReader())
            {
                List<BaivietModel> glstBaiviet = new List<BaivietModel>();
                if (dr.HasRows)
                {
                    while (dr.Read())
                    {
                        BaivietModel bv = new BaivietModel();
                        bv.ID = Convert.ToInt64(dr["ID"]);
                        bv.Mabaiviet = Convert.ToString(dr["Mabaiviet"]);
                        bv.Tenbaiviet = Convert.ToString(dr["Tenbaiviet"]);
                        bv.Shortcut = Convert.ToString(dr["shortcut"]);
                        bv.Noidung = Convert.ToString(dr["noidung"]);
                        bv.Anhdaidien = Convert.ToString(dr["anhdaidien"]);
                        bv.IDLoaibaiviet = Convert.ToInt64(dr["IDLoaibaiviet"]);
                        bv.Nguoiviet = Convert.ToString(dr["nguoiviet"]);
                        bv.Ngayviet = Convert.ToDateTime(dr["ngayviet"]);
                        glstBaiviet.Add(bv);
                    }
                    dr.Close();
                    sqlCmd.Connection.Close();
                }
                return glstBaiviet;
            }
        }

        public List<BaivietModel> getBaivietByID(long id)
        {
            sqlCmd.CommandType = CommandType.StoredProcedure;
            sqlCmd.CommandText = "sp_baiviet_SelectRow";
            sqlCmd.Parameters.Clear();

            sqlCmd.Parameters.Add("@ID", SqlDbType.BigInt).Value = id;
            sqlCmd.Connection.Close();
            sqlCmd.Connection.Open();

            using (SqlDataReader dr = sqlCmd.ExecuteReader())
            {
                List<BaivietModel> glstBaiviet = new List<BaivietModel>();
                if (dr.HasRows)
                {
                    while (dr.Read())
                    {
                        BaivietModel bv = new BaivietModel();
                        bv.ID = Convert.ToInt64(dr["ID"]);
                        bv.Mabaiviet = Convert.ToString(dr["Mabaiviet"]);
                        bv.Tenbaiviet = Convert.ToString(dr["Tenbaiviet"]);
                        bv.Shortcut = Convert.ToString(dr["shortcut"]);
                        bv.Noidung = Convert.ToString(dr["noidung"]);
                        bv.Anhdaidien = Convert.ToString(dr["anhdaidien"]);
                        bv.IDLoaibaiviet = Convert.ToInt64(dr["IDLoaibaiviet"]);
                        bv.Nguoiviet = Convert.ToString(dr["nguoiviet"]);
                        bv.Ngayviet = Convert.ToDateTime(dr["ngayviet"]);
                        glstBaiviet.Add(bv);
                    }
                    dr.Close();
                    sqlCmd.Connection.Close();
                }
                return glstBaiviet;
            }
        }

        public List<BaivietModel> getTop3BaivietByLoai(long idLoaibai)
        {
            sqlCmd.CommandType = CommandType.StoredProcedure;
            sqlCmd.CommandText = "sp_baiviet_SelectTop3ByLoaibai";
            sqlCmd.Parameters.Clear();

            sqlCmd.Parameters.Add("@IDLoaibaiviet", SqlDbType.BigInt).Value = idLoaibai;
            sqlCmd.Connection.Close();
            sqlCmd.Connection.Open();

            using (SqlDataReader dr = sqlCmd.ExecuteReader())
            {
                List<BaivietModel> glstBaiviet = new List<BaivietModel>();
                if (dr.HasRows)
                {
                    while (dr.Read())
                    {
                        BaivietModel bv = new BaivietModel();
                        bv.ID = Convert.ToInt64(dr["ID"]);
                        bv.Mabaiviet = Convert.ToString(dr["Mabaiviet"]);
                        bv.Tenbaiviet = Convert.ToString(dr["Tenbaiviet"]);
                        bv.Shortcut = Convert.ToString(dr["shortcut"]);
                        bv.Noidung = Convert.ToString(dr["noidung"]);
                        bv.Anhdaidien = Convert.ToString(dr["anhdaidien"]);
                        bv.IDLoaibaiviet = Convert.ToInt64(dr["IDLoaibaiviet"]);
                        bv.Nguoiviet = Convert.ToString(dr["nguoiviet"]);
                        bv.Ngayviet = Convert.ToDateTime(dr["ngayviet"]);
                        glstBaiviet.Add(bv);
                    }
                    dr.Close();
                    sqlCmd.Connection.Close();
                }
                return glstBaiviet;
            }
        }

        public string themmoiBaiviet(BaivietModel bv)
        {
            sqlCmd.CommandType = CommandType.StoredProcedure;
            sqlCmd.CommandText = "sp_baiviet_Insert";
            sqlCmd.Parameters.Clear();

            sqlCmd.Parameters.Add("@IDLoaibaiviet", SqlDbType.BigInt).Value = bv.IDLoaibaiviet;
            sqlCmd.Parameters.Add("@Mabaiviet", SqlDbType.NVarChar).Value = bv.Mabaiviet;
            sqlCmd.Parameters.Add("@Tenbaiviet", SqlDbType.NVarChar).Value = bv.Tenbaiviet;
            sqlCmd.Parameters.Add("@shortcut", SqlDbType.NVarChar).Value = bv.Shortcut;
            sqlCmd.Parameters.Add("@noidung", SqlDbType.NVarChar).Value = bv.Noidung;
            sqlCmd.Parameters.Add("@anhdaidien", SqlDbType.NVarChar).Value = bv.Anhdaidien;
            sqlCmd.Parameters.Add("@ngayviet", SqlDbType.DateTime).Value = bv.Ngayviet;
            sqlCmd.Parameters.Add("@nguoiviet", SqlDbType.NVarChar).Value = bv.Nguoiviet;
            sqlCmd.Connection.Open();
            sqlCmd.ExecuteNonQuery();
            sqlCmd.Connection.Close();

            return sqlCmd.Parameters["@Mabaiviet"].Value.ToString();
        }

        public string suaBaiviet(BaivietModel bv)
        {
            sqlCmd.CommandType = CommandType.StoredProcedure;
            sqlCmd.CommandText = "sp_baiviet_Update";
            sqlCmd.Parameters.Clear();

            sqlCmd.Parameters.Add("@ID", SqlDbType.BigInt).Value = bv.ID;
            sqlCmd.Parameters.Add("@IDLoaibaiviet", SqlDbType.BigInt).Value = bv.IDLoaibaiviet;
            sqlCmd.Parameters.Add("@Mabaiviet", SqlDbType.NVarChar).Value = bv.Mabaiviet;
            sqlCmd.Parameters.Add("@Tenbaiviet", SqlDbType.NVarChar).Value = bv.Tenbaiviet;
            sqlCmd.Parameters.Add("@shortcut", SqlDbType.NVarChar).Value = bv.Shortcut;
            sqlCmd.Parameters.Add("@noidung", SqlDbType.NVarChar).Value = bv.Noidung;
            sqlCmd.Parameters.Add("@anhdaidien", SqlDbType.NVarChar).Value = bv.Anhdaidien;
            sqlCmd.Parameters.Add("@ngayviet", SqlDbType.DateTime).Value = bv.Ngayviet;
            sqlCmd.Parameters.Add("@nguoiviet", SqlDbType.NVarChar).Value = bv.Nguoiviet;
            sqlCmd.Connection.Open();
            sqlCmd.ExecuteNonQuery();
            sqlCmd.Connection.Close();

            return sqlCmd.Parameters["@Mabaiviet"].Value.ToString();
        }
    }
}
